CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int)
RETURNS TABLE
AS
RETURN
    WITH AssetGroupHeirarchy AS
    (
        SELECT ParentAssetGroupID, ChildAssetGroupID
        FROM AssetGroupAssetGroup
        WHERE ParentAssetGroupID = @assetGroupID  -- anchor member
        UNION ALL
        SELECT b.ParentAssetGroupID, a.ChildAssetGroupID -- recursive member
        FROM
            AssetGroupAssetGroup AS a JOIN
            AssetGroupHeirarchy AS b ON b.ChildAssetGroupID = a.ParentAssetGroupID
    )
    SELECT DISTINCT MeterID AS ID
    FROM
        MeterAssetGroup LEFT JOIN
        AssetGroupHeirarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHeirarchy.ChildAssetGroupID
    WHERE
        MeterAssetGroup.AssetGroupID = @assetGroupID OR
        MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHeirarchy)
GO